﻿using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;

using System.Text;
using Models;

namespace DAL
{
    public class D_Student
    {
        // BUG:出现group is null
        public bool updateGroupid(List<Student> students, int group_id)//对应D_Student.cs
        {
            int rows = 0;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update [dbo].[4_1_Student] set ");
            strSql.Append("group_id=@group_id where stu_id=@stu_id");

            foreach (Student stu in students)
            {
                SqlParameter[] parameters = {
                    new SqlParameter("@group_id", SqlDbType.Int,10),
                    new SqlParameter("@stu_id", SqlDbType.Int,11),
                };
                parameters[0].Value = group_id;
                parameters[1].Value = stu.stu_id;

                rows += SqlDbHelper.ExecuteNonQuery(strSql.ToString(), CommandType.Text, parameters);
            }
            return rows == students.Count ? true : false;
        }

        public bool updateGroupid(Student student)// 对应D_Student.cs的update_groupid_sugar(Student)
        {
            var rows = SqlSugarHelper.Db.Updateable(student)
                                       .Where(stu => stu.stu_id == student.stu_id)
                                       .ExecuteCommand ();
            return rows == 0 ? true : false;
        }

        public bool saveList(List<Student> students)
        {
            var count = SqlSugarHelper.Db.Storageable(students)
                          .ExecuteCommand();
            return count == students.Count ? true : false;
        }

        // 这里有原生sql
        public int releaseGroupid(StuGroup stuGroup)
        {
            string sqlcmd = $"update [dbo].[4_1_student] set group_id = 0, is_gleader = 0 where group_id={stuGroup.group_id}";
            var rows = SqlSugarHelper.Db.Ado.ExecuteCommand(sqlcmd) ;
            return rows;
        }

        public List<Student> getList(int grade, int major_id)
        {
            var lst = SqlSugarHelper.Db.Queryable<Student>()
                                .Where(stu => stu.grade == grade && stu.major_id == major_id).ToList();
            return lst;
        }

        public List<Student> getList()
        {
            var lst = SqlSugarHelper.Db.Queryable<Student>().ToList();
            return lst;
        }

        public List<Student> getPage(int page_index, int page_size, ref int totalCount)
        {
            return SqlSugarHelper.Db.Queryable<Student>().ToPageList(page_index, page_size, ref totalCount);
        }

        public List<Student> getList(StuGroup group)
        {
            var lst = SqlSugarHelper.Db.Queryable<Student>()
                            .Where(stu => stu.group_id ==  group.group_id).ToList();
            return lst;
        }

        public List<Student> getListNotSetGroup()// 对应D_Student.cs的GetList_wo_Group()
        {
            var lst = SqlSugarHelper.Db.Queryable<Student>()
                            .Where(stu => stu.group_id == 0).ToList();
            return lst;
        }

        // 登录功能
        //        public bool loginUser(Models.Student student)
        //        {
        //            var stu = SqlSugarHelper.Db.Queryable<Student>()
        //                        .Where(s => s.stu_no == student.stu_no  && s.pwd == student.pwd).ToList();
        //            if (stu.Count == 0)
        //                return false;
        //            // 这里有log info, 11-16-6:36
        //            LogInfo.Server_Time = SqlSugarHelper.Db.GetDate();

        //            LogInfo.student = stu[0];
        //#if true // 11-16-7:07 先不要打开
        //            if (stu[0].group_id != 0)
        //            {
        //                var stu_group = SqlSugarHelper.Db.Queryable<StuGroup>().Where(g => g.group_id == stu[0].group_id).ToList().First();
        //                LogInfo.stu_Group = stu_group;
        //            }
        //#endif
        //            return true;
        //        }

        public bool login_sugar(Student m_stu)
        {
            var student = SqlSugarHelper.Db.Queryable<Student>().Where(stu => stu.stu_no == m_stu.stu_no && stu.pwd == m_stu.pwd).ToList();
            if (student.Count == 0)
                return false;

            LogInfo.Server_Time = SqlSugarHelper.Db.GetDate();

            LogInfo.student = student[0];

            var sys_config = SqlSugarHelper.Db.Queryable<Sys_config>().ToList();

            if (sys_config.Count > 0)
            {
                LogInfo.Sys_Config.group_members = sys_config[0].group_members;
                LogInfo.Sys_Config.group_choice = sys_config[0].group_choice;
            }

            if (student[0].group_id != 0)
            {
                var stu_group = SqlSugarHelper.Db.Queryable<StuGroup>().Where(g => g.group_id == student[0].group_id).ToList().First();
                LogInfo.stu_Group = stu_group;
            }

            return true;
        }

        // 批量增加学生
        public bool add(List<Student> students)
        {
            var res = SqlSugarHelper.Db.Storageable(students).ExecuteCommand();
            return res == students.Count ? true : false;
        }

        public bool del(List<Student> students)
        {
            var res = SqlSugarHelper.Db.Deleteable(students).ExecuteCommand();
            return res == students.Count() ? true : false;
        }

    }
}
